{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loading lab table\n",
    "lab_df = pd.read_csv(\"hosp/labevents.csv.gz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loading ICU stays table\n",
    "icu_pt_df = pd.read_csv(\"icu/icustays.csv.gz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>stay_id</th>\n",
       "      <th>first_careunit</th>\n",
       "      <th>last_careunit</th>\n",
       "      <th>intime</th>\n",
       "      <th>outtime</th>\n",
       "      <th>los</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>39553978</td>\n",
       "      <td>Medical Intensive Care Unit (MICU)</td>\n",
       "      <td>Medical Intensive Care Unit (MICU)</td>\n",
       "      <td>2180-07-23 14:00:00</td>\n",
       "      <td>2180-07-23 23:50:47</td>\n",
       "      <td>0.410266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000980</td>\n",
       "      <td>26913865</td>\n",
       "      <td>39765666</td>\n",
       "      <td>Medical Intensive Care Unit (MICU)</td>\n",
       "      <td>Medical Intensive Care Unit (MICU)</td>\n",
       "      <td>2189-06-27 08:42:00</td>\n",
       "      <td>2189-06-27 20:38:27</td>\n",
       "      <td>0.497535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001217</td>\n",
       "      <td>24597018</td>\n",
       "      <td>37067082</td>\n",
       "      <td>Surgical Intensive Care Unit (SICU)</td>\n",
       "      <td>Surgical Intensive Care Unit (SICU)</td>\n",
       "      <td>2157-11-20 19:18:02</td>\n",
       "      <td>2157-11-21 22:08:00</td>\n",
       "      <td>1.118032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001217</td>\n",
       "      <td>27703517</td>\n",
       "      <td>34592300</td>\n",
       "      <td>Surgical Intensive Care Unit (SICU)</td>\n",
       "      <td>Surgical Intensive Care Unit (SICU)</td>\n",
       "      <td>2157-12-19 15:42:24</td>\n",
       "      <td>2157-12-20 14:27:41</td>\n",
       "      <td>0.948113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001725</td>\n",
       "      <td>25563031</td>\n",
       "      <td>31205490</td>\n",
       "      <td>Medical/Surgical Intensive Care Unit (MICU/SICU)</td>\n",
       "      <td>Medical/Surgical Intensive Care Unit (MICU/SICU)</td>\n",
       "      <td>2110-04-11 15:52:22</td>\n",
       "      <td>2110-04-12 23:59:56</td>\n",
       "      <td>1.338588</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id   hadm_id   stay_id  \\\n",
       "0    10000032  29079034  39553978   \n",
       "1    10000980  26913865  39765666   \n",
       "2    10001217  24597018  37067082   \n",
       "3    10001217  27703517  34592300   \n",
       "4    10001725  25563031  31205490   \n",
       "\n",
       "                                     first_careunit  \\\n",
       "0                Medical Intensive Care Unit (MICU)   \n",
       "1                Medical Intensive Care Unit (MICU)   \n",
       "2               Surgical Intensive Care Unit (SICU)   \n",
       "3               Surgical Intensive Care Unit (SICU)   \n",
       "4  Medical/Surgical Intensive Care Unit (MICU/SICU)   \n",
       "\n",
       "                                      last_careunit               intime  \\\n",
       "0                Medical Intensive Care Unit (MICU)  2180-07-23 14:00:00   \n",
       "1                Medical Intensive Care Unit (MICU)  2189-06-27 08:42:00   \n",
       "2               Surgical Intensive Care Unit (SICU)  2157-11-20 19:18:02   \n",
       "3               Surgical Intensive Care Unit (SICU)  2157-12-19 15:42:24   \n",
       "4  Medical/Surgical Intensive Care Unit (MICU/SICU)  2110-04-11 15:52:22   \n",
       "\n",
       "               outtime       los  \n",
       "0  2180-07-23 23:50:47  0.410266  \n",
       "1  2189-06-27 20:38:27  0.497535  \n",
       "2  2157-11-21 22:08:00  1.118032  \n",
       "3  2157-12-20 14:27:41  0.948113  \n",
       "4  2110-04-12 23:59:56  1.338588  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ICU stays table\n",
    "icu_pt_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>labevent_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>specimen_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>order_provider_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>ref_range_lower</th>\n",
       "      <th>ref_range_upper</th>\n",
       "      <th>flag</th>\n",
       "      <th>priority</th>\n",
       "      <th>comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45421181</td>\n",
       "      <td>51237</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 15:15:00</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.1</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45421181</td>\n",
       "      <td>51274</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 15:15:00</td>\n",
       "      <td>___</td>\n",
       "      <td>15.1</td>\n",
       "      <td>sec</td>\n",
       "      <td>9.4</td>\n",
       "      <td>12.5</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>VERIFIED.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50853</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-25 11:06:00</td>\n",
       "      <td>___</td>\n",
       "      <td>15.0</td>\n",
       "      <td>ng/mL</td>\n",
       "      <td>30.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NEW ASSAY IN USE ___: DETECTS D2 AND D3 25-OH ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50861</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 16:40:00</td>\n",
       "      <td>102</td>\n",
       "      <td>102.0</td>\n",
       "      <td>IU/L</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50862</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 16:40:00</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>g/dL</td>\n",
       "      <td>3.5</td>\n",
       "      <td>5.2</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   labevent_id  subject_id  hadm_id  specimen_id  itemid order_provider_id  \\\n",
       "0            1    10000032      NaN     45421181   51237            P28Z0X   \n",
       "1            2    10000032      NaN     45421181   51274            P28Z0X   \n",
       "2            3    10000032      NaN     52958335   50853            P28Z0X   \n",
       "3            4    10000032      NaN     52958335   50861            P28Z0X   \n",
       "4            5    10000032      NaN     52958335   50862            P28Z0X   \n",
       "\n",
       "             charttime            storetime value  valuenum valueuom  \\\n",
       "0  2180-03-23 11:51:00  2180-03-23 15:15:00   1.4       1.4      NaN   \n",
       "1  2180-03-23 11:51:00  2180-03-23 15:15:00   ___      15.1      sec   \n",
       "2  2180-03-23 11:51:00  2180-03-25 11:06:00   ___      15.0    ng/mL   \n",
       "3  2180-03-23 11:51:00  2180-03-23 16:40:00   102     102.0     IU/L   \n",
       "4  2180-03-23 11:51:00  2180-03-23 16:40:00   3.3       3.3     g/dL   \n",
       "\n",
       "   ref_range_lower  ref_range_upper      flag priority  \\\n",
       "0              0.9              1.1  abnormal  ROUTINE   \n",
       "1              9.4             12.5  abnormal  ROUTINE   \n",
       "2             30.0             60.0  abnormal  ROUTINE   \n",
       "3              0.0             40.0  abnormal  ROUTINE   \n",
       "4              3.5              5.2  abnormal  ROUTINE   \n",
       "\n",
       "                                            comments  \n",
       "0                                                NaN  \n",
       "1                                          VERIFIED.  \n",
       "2  NEW ASSAY IN USE ___: DETECTS D2 AND D3 25-OH ...  \n",
       "3                                                NaN  \n",
       "4                                                NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lab table\n",
    "lab_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>label</th>\n",
       "      <th>fluid</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50801</td>\n",
       "      <td>Alveolar-arterial Gradient</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50802</td>\n",
       "      <td>Base Excess</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50803</td>\n",
       "      <td>Calculated Bicarbonate, Whole Blood</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50804</td>\n",
       "      <td>Calculated Total CO2</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50805</td>\n",
       "      <td>Carboxyhemoglobin</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   itemid                                label  fluid   category\n",
       "0   50801           Alveolar-arterial Gradient  Blood  Blood Gas\n",
       "1   50802                          Base Excess  Blood  Blood Gas\n",
       "2   50803  Calculated Bicarbonate, Whole Blood  Blood  Blood Gas\n",
       "3   50804                 Calculated Total CO2  Blood  Blood Gas\n",
       "4   50805                    Carboxyhemoglobin  Blood  Blood Gas"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading and displaying item id within lab table\n",
    "lab_id = pd.read_csv('hosp/d_labitems.csv.gz')\n",
    "lab_id.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>label</th>\n",
       "      <th>fluid</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>50809</td>\n",
       "      <td>Glucose</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>50842</td>\n",
       "      <td>Glucose, Ascites</td>\n",
       "      <td>Ascites</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>50931</td>\n",
       "      <td>Glucose</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>51022</td>\n",
       "      <td>Glucose, Joint Fluid</td>\n",
       "      <td>Joint Fluid</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>51034</td>\n",
       "      <td>Glucose, Body Fluid</td>\n",
       "      <td>Other Body Fluid</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>51053</td>\n",
       "      <td>Glucose, Pleural</td>\n",
       "      <td>Pleural</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>51084</td>\n",
       "      <td>Glucose, Urine</td>\n",
       "      <td>Urine</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>51478</td>\n",
       "      <td>Glucose</td>\n",
       "      <td>Urine</td>\n",
       "      <td>Hematology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908</th>\n",
       "      <td>51790</td>\n",
       "      <td>Glucose, CSF</td>\n",
       "      <td>Cerebrospinal Fluid</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1034</th>\n",
       "      <td>51941</td>\n",
       "      <td>Glucose, Stool</td>\n",
       "      <td>Stool</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1074</th>\n",
       "      <td>51981</td>\n",
       "      <td>Glucose</td>\n",
       "      <td>Urine</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1120</th>\n",
       "      <td>52027</td>\n",
       "      <td>Glucose, Whole Blood</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1528</th>\n",
       "      <td>52569</td>\n",
       "      <td>Glucose</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      itemid                 label                fluid    category\n",
       "7      50809               Glucose                Blood   Blood Gas\n",
       "40     50842      Glucose, Ascites              Ascites   Chemistry\n",
       "129    50931               Glucose                Blood   Chemistry\n",
       "210    51022  Glucose, Joint Fluid          Joint Fluid   Chemistry\n",
       "222    51034   Glucose, Body Fluid     Other Body Fluid   Chemistry\n",
       "241    51053      Glucose, Pleural              Pleural   Chemistry\n",
       "272    51084        Glucose, Urine                Urine   Chemistry\n",
       "638    51478               Glucose                Urine  Hematology\n",
       "908    51790          Glucose, CSF  Cerebrospinal Fluid   Chemistry\n",
       "1034   51941        Glucose, Stool                Stool   Chemistry\n",
       "1074   51981               Glucose                Urine   Chemistry\n",
       "1120   52027  Glucose, Whole Blood                Blood   Blood Gas\n",
       "1528   52569               Glucose                Blood   Chemistry"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check which glucose we should use --> itemid: 50931 (mapped to LOINC code 2345-7)\n",
    "lab_id[lab_id['label'].str.contains(\"Glucose\", na=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>label</th>\n",
       "      <th>fluid</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>51478</td>\n",
       "      <td>Glucose</td>\n",
       "      <td>Urine</td>\n",
       "      <td>Hematology</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     itemid    label  fluid    category\n",
       "638   51478  Glucose  Urine  Hematology"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lab_id[lab_id['itemid']==51478]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>labevent_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>specimen_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>order_provider_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>ref_range_lower</th>\n",
       "      <th>ref_range_upper</th>\n",
       "      <th>flag</th>\n",
       "      <th>priority</th>\n",
       "      <th>comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>60</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73913913</td>\n",
       "      <td>50931</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 15:56:00</td>\n",
       "      <td>___</td>\n",
       "      <td>95.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>IF FASTING, 70-100 NORMAL, &gt;125 PROVISIONAL DI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>104</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43430170</td>\n",
       "      <td>50931</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2180-05-06 22:25:00</td>\n",
       "      <td>2180-05-06 23:16:00</td>\n",
       "      <td>___</td>\n",
       "      <td>109.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>STAT</td>\n",
       "      <td>IF FASTING, 70-100 NORMAL, &gt;125 PROVISIONAL DI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>151</td>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853.0</td>\n",
       "      <td>93044861</td>\n",
       "      <td>50931</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2180-05-07 05:05:00</td>\n",
       "      <td>2180-05-07 07:03:00</td>\n",
       "      <td>___</td>\n",
       "      <td>99.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>IF FASTING, 70-100 NORMAL, &gt;125 PROVISIONAL DI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>173</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41498752</td>\n",
       "      <td>50931</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-06-03 12:00:00</td>\n",
       "      <td>2180-06-03 13:04:00</td>\n",
       "      <td>___</td>\n",
       "      <td>127.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>IF FASTING, 70-100 NORMAL, &gt;125 PROVISIONAL DI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>186</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>86302346</td>\n",
       "      <td>50931</td>\n",
       "      <td>P260SK</td>\n",
       "      <td>2180-06-03 12:00:00</td>\n",
       "      <td>2180-06-03 13:04:00</td>\n",
       "      <td>___</td>\n",
       "      <td>122.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>IF FASTING, 70-100 NORMAL, &gt;125 PROVISIONAL DI...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     labevent_id  subject_id     hadm_id  specimen_id  itemid  \\\n",
       "59            60    10000032         NaN     73913913   50931   \n",
       "103          104    10000032         NaN     43430170   50931   \n",
       "150          151    10000032  22595853.0     93044861   50931   \n",
       "172          173    10000032         NaN     41498752   50931   \n",
       "185          186    10000032         NaN     86302346   50931   \n",
       "\n",
       "    order_provider_id            charttime            storetime value  \\\n",
       "59             P28Z0X  2180-03-23 11:51:00  2180-03-23 15:56:00   ___   \n",
       "103               NaN  2180-05-06 22:25:00  2180-05-06 23:16:00   ___   \n",
       "150               NaN  2180-05-07 05:05:00  2180-05-07 07:03:00   ___   \n",
       "172            P28Z0X  2180-06-03 12:00:00  2180-06-03 13:04:00   ___   \n",
       "185            P260SK  2180-06-03 12:00:00  2180-06-03 13:04:00   ___   \n",
       "\n",
       "     valuenum valueuom  ref_range_lower  ref_range_upper      flag priority  \\\n",
       "59       95.0    mg/dL             70.0            100.0       NaN  ROUTINE   \n",
       "103     109.0    mg/dL             70.0            100.0  abnormal     STAT   \n",
       "150      99.0    mg/dL             70.0            100.0       NaN  ROUTINE   \n",
       "172     127.0    mg/dL             70.0            100.0  abnormal  ROUTINE   \n",
       "185     122.0    mg/dL             70.0            100.0  abnormal  ROUTINE   \n",
       "\n",
       "                                              comments  \n",
       "59   IF FASTING, 70-100 NORMAL, >125 PROVISIONAL DI...  \n",
       "103  IF FASTING, 70-100 NORMAL, >125 PROVISIONAL DI...  \n",
       "150  IF FASTING, 70-100 NORMAL, >125 PROVISIONAL DI...  \n",
       "172  IF FASTING, 70-100 NORMAL, >125 PROVISIONAL DI...  \n",
       "185  IF FASTING, 70-100 NORMAL, >125 PROVISIONAL DI...  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check glucose (blood, chemistry, 50931)\n",
    "lab_df[lab_df['itemid']==50931].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>labevent_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>specimen_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>order_provider_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>ref_range_lower</th>\n",
       "      <th>ref_range_upper</th>\n",
       "      <th>flag</th>\n",
       "      <th>priority</th>\n",
       "      <th>comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>391367</th>\n",
       "      <td>391898</td>\n",
       "      <td>10034253</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64602824</td>\n",
       "      <td>52569</td>\n",
       "      <td>P40IUG</td>\n",
       "      <td>2151-07-29 12:50:00</td>\n",
       "      <td>2151-07-29 13:06:00</td>\n",
       "      <td>104</td>\n",
       "      <td>104.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>STAT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391388</th>\n",
       "      <td>391919</td>\n",
       "      <td>10034253</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88635316</td>\n",
       "      <td>52569</td>\n",
       "      <td>P31PEN</td>\n",
       "      <td>2151-09-30 13:24:00</td>\n",
       "      <td>2151-09-30 13:37:00</td>\n",
       "      <td>97</td>\n",
       "      <td>97.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>STAT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>517119</th>\n",
       "      <td>517819</td>\n",
       "      <td>10046097</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16230427</td>\n",
       "      <td>52569</td>\n",
       "      <td>P056IR</td>\n",
       "      <td>2186-11-05 14:35:00</td>\n",
       "      <td>2186-11-05 14:38:00</td>\n",
       "      <td>106</td>\n",
       "      <td>106.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>STAT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>576779</th>\n",
       "      <td>577617</td>\n",
       "      <td>10049902</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75742106</td>\n",
       "      <td>52569</td>\n",
       "      <td>P59ABV</td>\n",
       "      <td>2149-10-28 14:48:00</td>\n",
       "      <td>2149-10-28 14:56:00</td>\n",
       "      <td>97</td>\n",
       "      <td>97.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>STAT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>952753</th>\n",
       "      <td>954083</td>\n",
       "      <td>10089199</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42403542</td>\n",
       "      <td>52569</td>\n",
       "      <td>P32KL8</td>\n",
       "      <td>2123-10-12 13:50:00</td>\n",
       "      <td>2123-10-12 13:53:00</td>\n",
       "      <td>87</td>\n",
       "      <td>87.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>STAT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        labevent_id  subject_id  hadm_id  specimen_id  itemid  \\\n",
       "391367       391898    10034253      NaN     64602824   52569   \n",
       "391388       391919    10034253      NaN     88635316   52569   \n",
       "517119       517819    10046097      NaN     16230427   52569   \n",
       "576779       577617    10049902      NaN     75742106   52569   \n",
       "952753       954083    10089199      NaN     42403542   52569   \n",
       "\n",
       "       order_provider_id            charttime            storetime value  \\\n",
       "391367            P40IUG  2151-07-29 12:50:00  2151-07-29 13:06:00   104   \n",
       "391388            P31PEN  2151-09-30 13:24:00  2151-09-30 13:37:00    97   \n",
       "517119            P056IR  2186-11-05 14:35:00  2186-11-05 14:38:00   106   \n",
       "576779            P59ABV  2149-10-28 14:48:00  2149-10-28 14:56:00    97   \n",
       "952753            P32KL8  2123-10-12 13:50:00  2123-10-12 13:53:00    87   \n",
       "\n",
       "        valuenum valueuom  ref_range_lower  ref_range_upper      flag  \\\n",
       "391367     104.0    mg/dL             70.0            100.0  abnormal   \n",
       "391388      97.0    mg/dL             70.0            100.0       NaN   \n",
       "517119     106.0    mg/dL             70.0            100.0  abnormal   \n",
       "576779      97.0    mg/dL             70.0            100.0       NaN   \n",
       "952753      87.0    mg/dL             70.0            100.0       NaN   \n",
       "\n",
       "       priority comments  \n",
       "391367     STAT      NaN  \n",
       "391388     STAT      NaN  \n",
       "517119     STAT      NaN  \n",
       "576779     STAT      NaN  \n",
       "952753     STAT      NaN  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check glucose (blood, chemistry, 52569)\n",
    "lab_df[lab_df['itemid']==52569].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 0 ns, total: 2 µs\n",
      "Wall time: 4.05 µs\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>labevent_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>specimen_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>order_provider_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "      <th>valueuom</th>\n",
       "      <th>ref_range_lower</th>\n",
       "      <th>ref_range_upper</th>\n",
       "      <th>flag</th>\n",
       "      <th>priority</th>\n",
       "      <th>comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50904</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 16:40:00</td>\n",
       "      <td>52</td>\n",
       "      <td>52.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52958335</td>\n",
       "      <td>50907</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 16:40:00</td>\n",
       "      <td>202</td>\n",
       "      <td>202.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>199.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>60</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73913913</td>\n",
       "      <td>50931</td>\n",
       "      <td>P28Z0X</td>\n",
       "      <td>2180-03-23 11:51:00</td>\n",
       "      <td>2180-03-23 15:56:00</td>\n",
       "      <td>___</td>\n",
       "      <td>95.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>IF FASTING, 70-100 NORMAL, &gt;125 PROVISIONAL DI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>104</td>\n",
       "      <td>10000032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43430170</td>\n",
       "      <td>50931</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2180-05-06 22:25:00</td>\n",
       "      <td>2180-05-06 23:16:00</td>\n",
       "      <td>___</td>\n",
       "      <td>109.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>abnormal</td>\n",
       "      <td>STAT</td>\n",
       "      <td>IF FASTING, 70-100 NORMAL, &gt;125 PROVISIONAL DI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>151</td>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853.0</td>\n",
       "      <td>93044861</td>\n",
       "      <td>50931</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2180-05-07 05:05:00</td>\n",
       "      <td>2180-05-07 07:03:00</td>\n",
       "      <td>___</td>\n",
       "      <td>99.0</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ROUTINE</td>\n",
       "      <td>IF FASTING, 70-100 NORMAL, &gt;125 PROVISIONAL DI...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     labevent_id  subject_id     hadm_id  specimen_id  itemid  \\\n",
       "15            16    10000032         NaN     52958335   50904   \n",
       "17            18    10000032         NaN     52958335   50907   \n",
       "59            60    10000032         NaN     73913913   50931   \n",
       "103          104    10000032         NaN     43430170   50931   \n",
       "150          151    10000032  22595853.0     93044861   50931   \n",
       "\n",
       "    order_provider_id            charttime            storetime value  \\\n",
       "15             P28Z0X  2180-03-23 11:51:00  2180-03-23 16:40:00    52   \n",
       "17             P28Z0X  2180-03-23 11:51:00  2180-03-23 16:40:00   202   \n",
       "59             P28Z0X  2180-03-23 11:51:00  2180-03-23 15:56:00   ___   \n",
       "103               NaN  2180-05-06 22:25:00  2180-05-06 23:16:00   ___   \n",
       "150               NaN  2180-05-07 05:05:00  2180-05-07 07:03:00   ___   \n",
       "\n",
       "     valuenum valueuom  ref_range_lower  ref_range_upper      flag priority  \\\n",
       "15       52.0    mg/dL              NaN              NaN       NaN  ROUTINE   \n",
       "17      202.0    mg/dL              0.0            199.0  abnormal  ROUTINE   \n",
       "59       95.0    mg/dL             70.0            100.0       NaN  ROUTINE   \n",
       "103     109.0    mg/dL             70.0            100.0  abnormal     STAT   \n",
       "150      99.0    mg/dL             70.0            100.0       NaN  ROUTINE   \n",
       "\n",
       "                                              comments  \n",
       "15                                                 NaN  \n",
       "17                                                 NaN  \n",
       "59   IF FASTING, 70-100 NORMAL, >125 PROVISIONAL DI...  \n",
       "103  IF FASTING, 70-100 NORMAL, >125 PROVISIONAL DI...  \n",
       "150  IF FASTING, 70-100 NORMAL, >125 PROVISIONAL DI...  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "\n",
    "# HDL: 50904, total cholesterol : 50907, glucose : 51478\n",
    "hdl_chol_glu_lab = lab_df[lab_df[\"itemid\"].isin([50904,50907,50931])]\n",
    "hdl_chol_glu_lab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3244066, 16)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hdl_chol_glu_lab.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "itemid\n",
       "50904    346\n",
       "50907     29\n",
       "50931    364\n",
       "Name: value, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_counts = hdl_chol_glu_lab[\"value\"].isna().groupby(hdl_chol_glu_lab[\"itemid\"]).sum()\n",
    "missing_counts"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
